package com.glomozda.machinerepairdao.repository.client;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.glomozda.machinerepairdao.domain.client.Client;

import static com.glomozda.machinerepairdao.repository.jdbc.DAOJDBCUtil.*;

@SuppressWarnings({"PMD.CommentRequired", "PMD.LawOfDemeter"})
@Repository("clientRepository")
public class ClientRepositoryJDBC implements ClientRepository {
	
	private final transient DataSource ds;

    @Autowired    
    public ClientRepositoryJDBC(final DataSource ds) {
        this.ds = ds;
    }
    
    @Override
    public Client find(final Integer id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Client client = null;

        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement("select * from Clients where clients_id=?");
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
            	client = map(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(preparedStatement);
            close(connection);
        }

        return client;
    }

    @Override
    public List<Client> findAll() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        final List<Client> clients = new ArrayList<Client>();

        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement("select * from Clients");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
            	clients.add(map(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(preparedStatement);
            close(connection);
        }

        return clients;
    }

    @Override
    public boolean create(final Client client) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int clientsQuantity = 0;
        
        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement("select count(*) from Clients");
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
            	clientsQuantity = resultSet.getInt(1);
            }
            close(preparedStatement);
            
            preparedStatement = connection.prepareStatement("INSERT INTO Clients "
                    + "(clients_id, name, user_id) VALUES (?, ?, ?)");
            preparedStatement.setInt(1, clientsQuantity + 1);
            preparedStatement.setString(2, client.getClientName());
            preparedStatement.setInt(3, client.getClientUserID());                        
            preparedStatement.execute();
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            close(preparedStatement);
            close(connection);
        }
        return false;
    }

    private Client map(final ResultSet resultSet) throws SQLException {
    	final Client client = new Client();
    	client.setClientID(resultSet.getInt(1));
    	client.setClientName(resultSet.getString(2));
    	client.setClientUserID(resultSet.getInt(3));    	
        return client;
    }

    @Override
    public Client findByName(final String clientName) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Client client = null;

        try {
            connection = ds.getConnection();
            preparedStatement = connection.
                    prepareStatement("select * from Clients where name = ?");
            preparedStatement.setString(1, clientName);
            resultSet = preparedStatement.executeQuery();
                      
            if (resultSet.next()) {
            	client = map(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(preparedStatement);
            close(connection);
        }

        return client;
    }
}
